package cn.edu.zjgsu.ito.service;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import cn.edu.zjgsu.ito.model.Major;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class AdminMajManageService {

	/**
	 * 根据上传的EXCEL表格，获取所有专业信息并存入数据库
	 * 黄凯航
	 * @throws IOException 
	 * @throws BiffException 
	 */
	public String readMajorTemplate(String fileName,String newFileName,String filePath) throws BiffException, IOException
	{
		Date date=new Date();
		DateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		int i=0; //列
		int j=1; //行
		int k=-1;
		int rows=0;
		int breakRows=1;
		int breakExcel=1;
		int breakK=0;
		int breakNo=0;
		int breakName=0;
		int baseOnNo=0;
		int baseOnName=0;
		int countSuccess=0;
		int countUpdate=0;
		int countFail=0;

		String error="";
		String breakMajorNo="";
		String breakMajorName="";
		String collegeName="";
		String type="Major Template（专业模板）";
		String uploader="Admin（教学秘书）";
		String uploadTime=format.format(date);

		Record uploadFileRecord=new Record().set("type", type)
				.set("fileName", fileName)
				.set("newFileName", newFileName)
				.set("filePath", filePath)
				.set("uploader", uploader)
				.set("uploadTime", uploadTime);
		Db.save("upload_file", uploadFileRecord);

		Workbook rwb=null;
		Cell[] cells=new Cell[3];
		InputStream stream=new FileInputStream(filePath+"\\"+newFileName);
		rwb=Workbook.getWorkbook(stream);
		Sheet sheet=rwb.getSheet(0);
		cells[0]=sheet.getCell(0,j);
		cells[1]=sheet.getCell(1,j);
		rows=sheet.getRows();

		//检测EXCEL表中所有专业编号是否有冲突
		//提取所有专业编号和专业名称
		String[] majorNos=new String[rows-1];
		String[] majorNames=new String[rows-1];

		while(true)
		{
			breakRows++;

			if(cells[0].getContents().trim().equals("")||cells[0].getType()==CellType.EMPTY||
					cells[1].getContents().trim().equals("")||cells[1].getType()==CellType.EMPTY)
			{
				if(breakRows==rows)
					break;
				j++;
				cells[0]=sheet.getCell(0, j);
				cells[1]=sheet.getCell(1, j);
				continue;
			}
			k++;
			majorNos[k]=cells[0].getContents().trim();
			majorNames[k]=cells[1].getContents().trim();
			breakK=k;
			if(breakRows==rows)
				break;
			j++;
			cells[0]=sheet.getCell(0, j);
			cells[1]=sheet.getCell(1, j);
		}

		//检测冲突
		for(j=0;j<=breakK;j++)
		{
			if(breakExcel==0)
				break;
			for(k=j+1;k<=breakK;k++)
			{
				if(majorNos[j].equals(majorNos[k]))
				{
					breakExcel=0;
					breakMajorNo=majorNos[j];
					breakNo=1;
					break;
				}
				if(majorNames[j].equals(majorNames[k]))
				{
					breakExcel=0;
					breakMajorName=majorNames[j];
					breakName=1;
					break;
				}
			}
		}

		//可以执行入库
		if(breakExcel==1)
		{
			//数据再次初始化
			breakRows=1;
			j=1;
			cells[0]=sheet.getCell(0,j);
			cells[1]=sheet.getCell(1,j);
			cells[2]=sheet.getCell(2,j);

			while(true)
			{
				baseOnNo=0;
				baseOnName=0;
				breakRows++;
				//字段再次检测为空不计
				if(cells[0].getContents().trim().equals("")||cells[0].getType()==CellType.EMPTY||					
						cells[1].getContents().trim().equals("")||cells[1].getType()==CellType.EMPTY||
						cells[2].getContents().trim().equals("")||cells[2].getType()==CellType.EMPTY)
				{
					countFail++;
					j++;
					if(breakRows==rows)
						break;
					else
					{
						cells[0]=sheet.getCell(0,j);
						cells[1]=sheet.getCell(1,j);
						cells[2]=sheet.getCell(2,j);
					}
					continue;
				}

				//检测学院是否存在
				List<Record> collegeInfo=Db.find("select * from college where collegeCode=?",cells[2].getContents().trim());
				if(collegeInfo.size()==0)
				{
					//所填的学院不存在
					countFail++;
					j++;
					if(breakRows==rows)
						break;
					else
					{
						cells[0]=sheet.getCell(0,j);
						cells[1]=sheet.getCell(1,j);
						cells[2]=sheet.getCell(2,j);
					}
					continue;
				}
				collegeName=collegeInfo.get(0).get("collegeName");

				//从目标库提取所有记录
				List<Record> majorInfo=Db.find("select * from major");

				//检测目标专业编号是否已存在
				for(i=0;i<majorInfo.size();i++)
				{
					Record singleMajorInfo=majorInfo.get(i);
					String majorNo=singleMajorInfo.get("majorNo");
					if(majorNo.equals(cells[0].getContents().trim()))
					{
						List<Record> majortemp=Db.find("select * from major where majorName=?",cells[1].getContents().trim());
						if(majortemp.size()==0)
						{
							baseOnNo=1;
							break;
						}
						else
						{
							countFail++;
							break;
						}
					}						
				}
				//检测目标专业名词是否已存在
				if(baseOnNo==0)
				{
					for(i=0;i<majorInfo.size();i++)
					{
						Record singleMajorInfo=majorInfo.get(i);
						String majorName=singleMajorInfo.get("majorName");
						if(majorName.equals(cells[1].getContents().trim()))
						{
							baseOnName=1;
							break;
						}
					}
				}

				//可以执行新增操作
				if(baseOnNo==0&&baseOnName==0)
				{
					Record majorRecord=new Record()
					.set("majorNo", cells[0].getContents().trim())
					.set("majorName", cells[1].getContents().trim())
					.set("collegeName", collegeName)
					.set("collegeCode", cells[2].getContents().trim());		
					Db.save("major", majorRecord);
					countSuccess++;
				}
				//存在，执行覆盖操作
				else
				{
					if(baseOnNo==1)
					{
						//根据编码进行覆盖
						Db.update("update major set majorName=?,collegeName=?,collegeCode=? where majorNo=?",
								cells[1].getContents().trim(),
								collegeName,
								cells[2].getContents().trim(),
								cells[0].getContents().trim());
						boolean WARMING=updateAllTablesByNo(cells[0].getContents().trim(),cells[1].getContents().trim());
						if(WARMING==false)
							System.out.println("出事了！！！");
						countUpdate++;
					}
					else
					{
						//根据名称进行覆盖
						if(baseOnName==1)
						{
							Db.update("update major set majorNo=?,collegeName=?,collegeCode=? where majorName=?",
									cells[0].getContents().trim(),
									collegeName,
									cells[2].getContents().trim(),
									cells[1].getContents().trim());
							boolean WARMING=updateAllTablesByName(cells[0].getContents().trim(),cells[1].getContents().trim());
							if(WARMING==false)
								System.out.println("出事了！！！");
							countUpdate++;
						}
					}
				}
				j++;
				if(breakRows==rows)
					break;
				else
				{
					cells[0]=sheet.getCell(0,j);
					cells[1]=sheet.getCell(1,j);
					cells[2]=sheet.getCell(2,j);
				}
			}
			error="成功导入"+countSuccess+"条记录;成功覆盖"+countUpdate+"条记录;导入失败"+countFail+"条记录";
			return error;  
		}
		else
		{
			if(breakNo==1)			
				error="所上传的EXCEL表中存在数据冲突——专业编号："+breakMajorNo;
			else
			{
				if(breakName==1)
					error="所上传的EXCEL表中存在数据冲突——专业名称："+breakMajorName;
			}
			return error;
		}	
	}

	/**
	 * 添加单个专业方法
	 * 黄凯航
	 * @param major
	 * @return
	 */
	public String addSingleMajor(Major major)
	{
		String majorNo=major.getStr("majorNo").trim();
		String majorName=major.getStr("majorName").trim();
		String collegeCode=major.getStr("collegeCode").trim();
		String error="";
		//检测字段是否为空
		if(majorNo.equals("")||majorNo==null||
				majorName.equals("")||majorName==null||
				collegeCode.equals("")||collegeCode==null)
		{
			error="请填写所有必填信息！";
			return error;
		}

		//获取学院信息
		List<Record> collegeInfo=Db.find("select * from college where collegeCode=?",collegeCode);
		String collegeName=collegeInfo.get(0).get("collegeName");
		major.set("collegeName", collegeName);

		List<Record> majors=Db.find("select * from major where majorNo=?",majorNo);
		List<Record> majortemp=Db.find("select * from major where majorName=?",majorName);
		if(majors.size()==0&&majortemp.size()==0)
		{
			major.save();
			error="新增成功";
		}
		if(majors.size()==1&&majortemp.size()==0)
		{
//			Db.update("update major set majorName=?,collegeName=?,collegeCode=? where majorNo=?",majorName,collegeName,collegeCode,majorNo);
//			boolean WARMING=updateAllTablesByNo(majorNo,majorName);
//			if(WARMING==false)
//				System.out.println("出事了！！！");
//			error="新增成功";
			error="新增失败："+majorNo+"已存在";
		}
		if(majors.size()==0&&majortemp.size()==1)
		{
			Db.update("update major set majorNo=?,collegeName=?,collegeCode=? where majorName=?",majorNo,collegeName,collegeCode,majorName);
			boolean WARMING=updateAllTablesByName(majorNo,majorName);
			if(WARMING==false)
				System.out.println("出事了！！！");
			error="新增成功";
		}
		if(majors.size()==1&&majortemp.size()==1)
			error="新增失败："+majorNo+"和"+majorName+"皆已存在";
		System.out.println(error);
		return error;
	}
	
	/**
	 * 更新单个专业记录
	 * 黄凯航
	 * @param major
	 * @return
	 */
	public String updateOrNot(Major major)
	{
		int id=major.getInt("id");
		String majorNo=major.getStr("majorNo").trim();
		String majorName=major.getStr("majorName").trim();
		String collegeCode=major.getStr("collegeCode").trim();
		String collegeName="";
		String error="";
		
		if(majorNo.equals("")||majorNo==null||
				majorName.equals("")||majorName==null||
				id<=0)		
		{
			error="请填写所有必填信息";
			return error;
		}

		//获取学院信息
		List<Record> collegeInfo=Db.find("select * from college where collegeCode=?",collegeCode);
		collegeName=collegeInfo.get(0).get("collegeName");
		major.set("collegeName", collegeName);
		System.out.println("()()()()()()()))");
		System.out.println("major");
		List<Record> majors=Db.find("select * from major where majorName=?",majorName);
		if(majors.size()==0)
		{
			major.update();
			boolean WARMING=updateAllTablesByNo(majorNo,majorName);
			if(WARMING==false)
				System.out.println("出事了！！！");
			error="修改成功";		
		}
		else
		{
			if(majors.size()==1)
			{
				if(majors.get(0).getInt("id")==id)
				{
					major.update();			
					boolean WARMING=updateAllTablesByNo(majorNo,majorName);
					if(WARMING==false)
						System.out.println("出事了！！！");
					error="修改成功";
				}
				else
					error="专业名称已存在，修改失败";
			}	
		}
		return error;
	}
	/**
	 * 删除专业方法
	 * @param majorName
	 */
	public void deleteOrNot(String majorNo)
	{
		int roster=1; 
		int studentGrade=1; 
		int studentInfo=1; 
		int majorTimetable=1;
		List<Record> rNullOrNot=Db.find("select * from roster where majorName=?",majorNo);
		List<Record> sgNullOrNot=Db.find("select * from student_grade where majorName=?",majorNo);
		List<Record> siNullOrNot=Db.find("select * from student_info where majorName=?",majorNo);
		List<Record> mtNullOrNot=Db.find("select * from major_timetable where majorName=?",majorNo);
		
		if(rNullOrNot.size()==0)
			roster=0;
		if(sgNullOrNot.size()==0)
			studentGrade=0;
		if(siNullOrNot.size()==0)
			studentInfo=0;
		if(mtNullOrNot.size()==0)
			majorTimetable=0;
		
		if(roster==0&&studentGrade==0&&studentInfo==0&&majorTimetable==0)
			Db.update("delete from major where majorNo=?",majorNo);	
	}

	/**
	 * 更新所有表格一
	 * 黄凯航
	 */
	public boolean updateAllTablesByNo(String majorNo,String majorName)
	{
		int caseOne = Db.update("update major_timetable set majorName=? where majorNo=?",majorName,majorNo);
		int caseTwo = Db.update("update roster set majorName=? where majorNo=?",majorName,majorNo);
		int caseThree = Db.update("update student_grade set majorName=? where majorNo=?",majorName,majorNo);
		int caseFour = Db.update("update student_info set majorName=? where majorNo=?",majorName,majorNo);

		if(caseOne>0&&caseTwo>0&&caseThree>0&&caseFour>0)
			return true;
		else
			return false;
	}

	/**
	 * 更新所有表格二
	 * 黄凯航
	 */
	public boolean updateAllTablesByName(String majorNo,String majorName)
	{
		int caseOne = Db.update("update major_timetable set majorNo=? where majorName=?",majorNo,majorName);
		int caseTwo = Db.update("update roster set majorNo=? where majorName=?",majorNo,majorName);
		int caseThree = Db.update("update student_grade set majorNo=? where majorName=?",majorNo,majorName);
		int caseFour = Db.update("update student_info set majorNo=? where majorName=?",majorNo,majorName);

		if(caseOne>0&&caseTwo>0&&caseThree>0&&caseFour>0)
			return true;
		else
			return false;
	}

}
